Hi All,

Could it be possible to use DAX with SUMMARIZE(SUMMARIZE(....)) query?

=====================

Fact table looks like: (per customer, they only have same value per each month, it's not related to order)

Month	CustomerID	OrderNum	Order Type	Value
2015/1	CUST001	S000001	New	1000
2015/1	CUST001	S000002	Upgrade	1000
2015/2	CUST001	S000003	New	50
2015/3	CUST001	S000004	Renew	25000
2015/3	CUST001	S000005	Renew	25000

Dimension table:

Value Range

0 - 1000

1001 - 10000

10001 - 50000

50001 +

=====================

And I would like to create a measure to find out"How many orders by each value range (by customer) ?"

by Month view

Month	Value Range	Order count
2015/1	0 - 1000	2
2015/2	0 - 1000	1
2015/3	10001 - 50000	2

(e.g. 2015/1: customer avg amount will be 1000)

============

But in Quarter view

Quarter	Value Range	Order count
2015Q1	1001 - 10000	5

(e.g. 2015/Q1: customer avg amount will be (1000+50+25000)/3 = 8683)

===================================================

So far I didn't find good way to fulfill with this, and here is my thought:

1st, use SUMMARIZE(Fact, Fact[CustomerID],Fact[Value]) to distinct table and get only 1 value per customer

2nd, use SUMMARIZE again to find out how many order created by customer, SUMMARIZE(Fact, Fact[CustomerID],"Order_count",DISTINCTCOUNT(Fact[OrderNum])) 

3rd, combine above 2 result and get Month/Quarter view like above

I'm just wondering if you could advice any good idea to handle this scenario, thanks in advance.

Best Regards,

Jackie

• Edited by Jackie_tw 4 hours 30 minutes ago